package helperclasses;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.sun.corba.se.spi.orbutil.fsm.Guard.Result;

public class MyDataColector {

	public MyDataColector() {

	}

	private Connection getConnection() throws SQLException {
		DataSource ds = DbUtil.getInstance().getDataSource();
		Connection conn = ds.getConnection();
		return conn;
	}

	// Get all the suppliers names
	public ArrayList<String> getFurnizorNames() throws SQLException {
		ArrayList<String> fnList = new ArrayList<String>();
		Connection conn = getConnection();
		Statement st = conn.createStatement();
		String sql;

		sql = "SELECT nume FROM furnizori";

		try {
			ResultSet result = st.executeQuery(sql);
			while (result.next()) {
				fnList.add(result.getString("nume"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();
		}

		return fnList;
	}

	// Get company Id using the company name
	public int getCompanyId(String name) throws SQLException {
		Connection conn = getConnection();
		Statement st = conn.createStatement();
		String sql;
		int rezultat = 0;
		sql = "SELECT company_id FROM company WHERE nume='" + name + "'";

		try {
			ResultSet result = st.executeQuery(sql);
			while (result.next()) {
				rezultat = Integer.parseInt(result.getString("company_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();
		}
		return rezultat;
	}

	// Get restaurant id using the restaurant name
	public int getRestaurantId(String name) throws SQLException {
		Connection conn = getConnection();
		Statement st = conn.createStatement();
		String sql;
		int rezultat = 0;
		sql = "SELECT restaurant_id FROM restaurant WHERE nume='" + name + "'";

		try {
			ResultSet result = st.executeQuery(sql);
			while (result.next()) {
				rezultat = Integer.parseInt(result.getString("restaurant_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();
		}
		return rezultat;
	}

	// Get gestionar id using gestionar name
	public int getGestionarId(String name) throws SQLException {
		Connection conn = getConnection();
		Statement st = conn.createStatement();
		String sql;
		int rezultat = 0;
		sql = "SELECT gestionar_id FROM gestionar WHERE nume='" + name + "'";

		try {
			ResultSet result = st.executeQuery(sql);
			while (result.next()) {
				rezultat = Integer.parseInt(result.getString("gestionar_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();
		}
		return rezultat;
	}

	// Get furnizor id using furnizor name
	public int getFurnizorId(String name) throws SQLException {
		Connection conn = getConnection();
		Statement st = conn.createStatement();
		String sql;
		int rezultat = 0;
		sql = "SELECT furnizor_id FROM furnizori WHERE nume='" + name + "'";

		try {
			ResultSet result = st.executeQuery(sql);
			while (result.next()) {
				rezultat = Integer.parseInt(result.getString("furnizor_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();
		}
		return rezultat;
	}

	// Get unitate masura id using name
	public int getUnitateMasuraId(String um) throws SQLException {
		String sql;
		ResultSet result;
		Connection conn = getConnection();
		Statement st = conn.createStatement();

		sql = "SELECT unitate_masura_id FROM unitate_masura WHERE tip='" + um + "'";
		int umId = 0;
		try {
			result = st.executeQuery(sql);
			while (result.next()) {
				umId = Integer.parseInt(result.getString("unitate_masura_id"));
			}
			result.close();
			return umId;
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();
		}
		return umId;
	}

	// Get product id using product name
	public int getProductId(String productName) throws SQLException {
		String sql;
		ResultSet result;
		Connection conn = getConnection();
		Statement st = conn.createStatement();

		sql = "SELECT produs_id FROM produse WHERE nume ='" + productName + "'";
		int umId = 0;
		try {
			result = st.executeQuery(sql);
			while (result.next()) {
				umId = Integer.parseInt(result.getString("produs_id"));
			}
			result.close();
			return umId;
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();
		}
		return umId;
	}

	// Get product id using product name
	public String getProductName(int productId) throws SQLException {
		String sql;
		ResultSet result;
		Connection conn = getConnection();
		Statement st = conn.createStatement();

		sql = "SELECT nume FROM produse WHERE produs_id =" + productId;
		String prodName = "";
		try {
			result = st.executeQuery(sql);
			while (result.next()) {
				prodName = result.getString("nume");
			}
			result.close();
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();

		}
		return prodName;
	}

	public List<Bill> getBillsList() throws SQLException {
		String sql;
		ResultSet result;
		Connection conn = getConnection();
		Statement st = conn.createStatement();
		List<Bill> lfac = new ArrayList<Bill>();
		sql = "SELECT factura_id, cod_factura, data_factura, numar_nir, is_type1, is_type2 FROM facturi ORDER BY data_factura DESC";

		try {
			result = st.executeQuery(sql);
			while (result.next()) {
				Bill fac = new Bill();

				fac.setFacturaId(Integer.parseInt(result.getString("factura_id")));
				fac.setCodFactura(result.getString("cod_factura"));
				fac.setDataFactura(result.getString("data_factura"));
				fac.setNirNumber(Integer.parseInt(result.getString("numar_nir")));
				fac.setIsType1(result.getString("is_type1"));
				fac.setIsType2(result.getString("is_type2"));

				lfac.add(fac);
			}
			result.close();
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();
		}
		return lfac;
	}

	public int getNumarNir() throws SQLException {
		Connection conn = getConnection();
		Statement st = conn.createStatement();
		String sql;
		int rezultat = 0;
		sql = "SELECT MAX(numar_nir) FROM facturi";

		try {
			ResultSet result = st.executeQuery(sql);
			while (result.next()) {
				rezultat = Integer.parseInt(result.getString(1));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			st.close();
			conn.close();
		}
		return rezultat;
	}

	public List<Furnizori> getFurnizor(int furnizorId) {
		Connection conn;
		List<Furnizori> furn = new ArrayList<Furnizori>();
		String sql;
		try {
			conn = getConnection();
			Statement st = conn.createStatement();
			if (furnizorId == -1) {
				sql = "SELECT * FROM furnizori";
			}
			else{
				sql = "SELECT * FROM furnizori WHERE furnizor_id=" + furnizorId;
			}
			ResultSet result = st.executeQuery(sql);
			while (result.next()) {
				Furnizori fu = new Furnizori();
				fu.setFurnizorId(Integer.parseInt(result.getString("furnizor_id")));
				fu.setFurnizorName(result.getString("nume"));
				fu.setFurnizorAddress(result.getString("adresa"));
				fu.setFurnizorTelephone(result.getString("telefon"));
				furn.add(fu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return furn;
	}

}
